import math
import time
from flask_sqlalchemy import SQLAlchemy
from flask import Flask, render_template, request, url_for, flash, redirect, g, session
from flask_login import login_user, login_required, UserMixin, LoginManager, logout_user
from flask_wtf import FlaskForm as Form
from wtforms import StringField, PasswordField
from wtforms.validators import DataRequired

app = Flask(__name__)

app.config['SECRET_KEY'] = 'maishu is fat again, 555'
# app.config['SQLALCHEMY_DATABASE_URI'] = SQLALCHEMY_DATABASE_URI
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + './database.db'
# 跟踪对象的修改
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app=app)

login_manager = LoginManager(app)
login_manager.login_view = 'login'


class LoginForm(Form):
    accountNumber = StringField(
        'accountNumber', validators=[DataRequired('accountNumber is null')])
    password = PasswordField('password',
                             validators=[DataRequired('password is null')])


class Posts(db.Model):
    # 给表重新定义一个名称，默认名称是类名的小写，比如该类默认的表名是user。
    __tablename__ = "posts"
    id = db.Column('id', db.Integer, primary_key=True)
    title = db.Column('title', db.String(32), unique=True)
    created = db.Column('created', db.String(32), unique=True)
    content = db.Column('content', db.String(32), unique=True)

    def __repr__(self):
        return "Posts: %s %s %s %s" % (self.id, self.created, self.title,
                                       self.content)


class User(db.Model, UserMixin):
    user_id = db.Column('user_id', db.Integer, primary_key=True)
    accountNumber = db.Column('accountNumber', db.String(200), unique=True)
    password = db.Column('password', db.String(50), unique=True)
    name = db.Column('name', db.String(20), unique=True)

    __tablename__ = 'users'

    def __init__(self, user_id=None, account_number=None, password=None, name="anonymous"):
        self.user_id = user_id
        self.accountNumber = account_number
        self.password = password
        self.name = name

    def is_authenticated(self):
        return True

    def is_active(self):
        return True

    def is_anonymous(self):
        return False

    def get_id(self):
        return str(self.user_id)

    def __repr__(self):
        return '<User %r>' % (self.accountNumber)


# 创建一个函数用来获取数据库链接
# def get_db_connection():
#     # 创建数据库链接到database.db文件
#     conn = sqlite3.connect('database.db')
#     # 设置数据的解析方法，有了这个设置，就可以像字典一样访问每一列数据
#     conn.row_factory = sqlite3.Row
#     return conn


def get_post(post_id):
    post = Posts.query.filter_by(id=post_id).one()
    return post

    # conn = get_db_connection()
    # post = conn.execute('SELECT * FROM posts WHERE id = ?',
    #                     (post_id,)).fetchone()
    # conn.close()
    # return post


@app.before_request
def before_request():
    pass


@app.route('/test/<int:id>')
def test(id):
    test = Posts.query.filter_by(id=id)
    print(test)
    return render_template('index.html', posts=test)


@app.route('/')
def index():
    post = Posts.query.order_by(Posts.id.desc()).limit(5)
    return render_template('index.html', posts=post)


@app.route('/posts/<int:post_id>')
def post(post_id):
    post = get_post(post_id)
    return render_template('post.html', post=post)


@app.route('/about-us')
def about():
    return render_template('about-us.html')


@app.route('/pricing')
def pricing():
    return render_template('pricing.html')


@app.route('/features')
def features():
    return render_template('features.html')


@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id))


@app.route('/login', methods=['GET', 'POST'])
def login():
    form = LoginForm()
    if request.method == 'POST':
        if not form.validate_on_submit():
            return render_template('login.html', form=form)

        user = User.query.filter(User.accountNumber == form.accountNumber.data,
                                 User.password == form.password.data).first()
        if user:
            login_user(user)
            return redirect(url_for('admin'))

    return render_template('login.html', form=form)


@app.route('/posts/new', methods=('GET', 'POST'))
@login_required
def new():
    if request.method == 'POST':
        title = request.form['title']
        content = request.form['content']
        now = time.strftime('%Y-%m-%d %H:%M:%S')

        if not title:
            flash('标题不能为空!')
        elif not content:
            flash('内容不能为空')
        else:
            postnew = Posts(title=title, content=content, created=now)
            db.session.add(postnew)
            db.session.commit()
            # conn = get_db_connection()
            # conn.execute('INSERT INTO posts (title, content) VALUES (?, ?)',
            #              (title, content))
            # conn.commit()
            # conn.close()
            return redirect(url_for('admin'))
    return render_template('new.html')


@app.route('/posts/<int:id>/delete', methods=('POST',))
@login_required
def delete(id):
    posts = Posts.query.filter_by(id=id).first()
    db.session.delete(posts)
    db.session.commit()
    # conn = get_db_connection()
    # conn.execute('DELETE FROM posts WHERE id = ?', (id,))
    # conn.commit()
    # conn.close()
    # flash('"{}" 删除成功!'.format(post['title']))
    flash('删除成功!')
    return redirect(url_for('admin'))


@app.route('/posts/<int:id>/edit', methods=('GET', 'POST'))
@login_required
def edit(id):
    post = get_post(id)
    if request.method == 'POST':
        title = request.form['title']
        content = request.form['content']

        if not title:
            flash('Title is required!')
        else:
            post.title = title
            post.content = content
            post.id = id
            db.session.commit()
        # conn = get_db_connection()
        # conn.execute(
        #     'UPDATE posts SET title = ?, content = ?'
        #     ' WHERE id = ?', (title, content, id))
        # conn.commit()
        # conn.close()
        return redirect(url_for('admin'))
    return render_template('edit.html', post=post)


@app.route('/postlistnew')
def postlistnew():
    # 获取page参数 默认为1
    page = int(request.args.get('page', 1))
    # 获取每页显示数据条数默认为2
    per_page = int(request.args.get('perpage', 5))
    # 从数据库查询数据
    paginates = Posts.query.order_by(Posts.id.desc()).paginate(page=page,
                                                               per_page=per_page)

    stus = paginates.items

    # totalpage为总页面数
    totalpage = math.ceil(paginates.total / per_page)
    return render_template('postlistnew.html',
                           paginate=paginates,
                           stus=stus,
                           totalpage=totalpage)


@app.route('/admin', methods=('GET', 'POST'))
@login_required
def admin():
    # 获取page参数 默认为1
    page = int(request.args.get('page', 1))
    # 获取每页显示数据条数默认为2
    per_page = int(request.args.get('perpage', 5))
    # 从数据库查询数据
    paginates = Posts.query.order_by(Posts.id.desc()).paginate(page=page,
                                                               per_page=per_page)

    stus = paginates.items
    # totalpage为总页面数
    totalpage = math.ceil(paginates.total / per_page)

    # posts = Posts.query.order_by(Posts.id.desc())
    # posts = Posts.query.order_by(Posts.id.desc()).limit(10)

    # # 调用上面的函数，获取链接
    # conn = get_db_connection()
    # # 查询所有数据，放到变量posts中
    # posts = conn.execute('SELECT *   FROM posts ').fetchall()
    # conn.close()

    return render_template('admin.html',
                           paginate=paginates,
                           stus=stus,
                           totalpage=totalpage)


@app.route('/logout')
def logout():
    """ 退出登录 """
    logout_user()
    flash('欢迎下次再来', 'success')
    return redirect(url_for('login'))
